using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Reflection;
using DataObject.Core;
using System.Diagnostics;
using DataObject.Database;

namespace DataObject.DataAccess
{
    public partial class GuestDAO : DataBaseProcessBase<Guest, OBSDataContext>
    {
        /// <summary>
        /// Create Instance
        /// </summary>
        /// <returns></returns>
        public static GuestDAO CreateInstant()
        {
            return new GuestDAO();
        }

        /// <summary>
        /// Get agent by userid
        /// </summary>
        /// <param name="pGuestID"></param>
        /// <returns></returns>
        public Guest GetGuestByUserId(Guid pUserID)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                List<Guest> ls = db.Guests.Where(a => a.UserId.Equals(pUserID)).ToList();
                if (ls != null && ls.Count > 0)
                {
                    return ls[0];
                }
                return null;
            }
        }

        public bool AddGuest(Guest entity)
        {
            try
            {
                using (OBSDataContext db = new OBSDataContext(base.connectionString))
                {
                    if (entity.GuestType == 1)
                    {
                        double i = 0;

                        var id = db.Guests.Where(g => g.GuestID.StartsWith("WI/") && g.GuestType == 1).Max(g => g.GuestID);
                        if (id != null)
                        {
                            if (id.Length > 3)
                            {
                                bool b = Double.TryParse(id.Substring(3), out i);
                                if (b)
                                    i++;
                            }
                        }
                        id = "WI/" + i.ToString().PadLeft(8, '0');
                        entity.GuestID = id;
                    }
                    Add(entity);
                }
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Get agent by id
        /// </summary>
        /// <param name="pGuestID"></param>
        /// <returns></returns>
        public Guest GetAgentById(string pAgentID)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                return db.Guests.SingleOrDefault(a => a.GuestID.Equals(pAgentID) && a.GuestType == 0);
            }
        }

        /// <summary>
        /// Select list to search agent
        /// </summary>
        /// <param name="GuestID"></param>
        /// <param name="Name"></param>
        /// <param name="Email"></param>
        /// <param name="AddressLine1"></param>
        /// <param name="AddressLine2"></param>
        /// <param name="Tel"></param>
        /// <param name="Fax"></param>
        /// <param name="SortClause"></param>
        /// <returns></returns>
        public List<Guest> SelectListItem(string GuestID, string Name, string Email, string AddressLine1, string AddressLine2, string Tel, string Fax, HashSet<string> SortClause)
        {
            int Count = 0;
            return SelectListItem(GuestID, Name, Email, AddressLine1, AddressLine2, Tel, Fax, null, null, out Count, SortClause);
        }

        /// <summary>
        /// Select list item
        /// </summary>
        /// <param name="GuestID"></param>
        /// <param name="Name"></param>
        /// <param name="Email"></param>
        /// <param name="AddressLine1"></param>
        /// <param name="AddressLine2"></param>
        /// <param name="Tel"></param>
        /// <param name="Fax"></param>
        /// <param name="Page"></param>
        /// <param name="Row"></param>
        /// <param name="Count"></param>
        /// <param name="SortClause"></param>
        /// <returns></returns>
        public List<Guest> SelectListItem(string GuestID, string Name, string Email, string AddressLine1, string AddressLine2, string Tel, string Fax, int? Page, int? Row, out int Count, HashSet<string> SortClause)
        {
            List<Guest> list = new List<Guest>();

            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                // Only get agent type
                var query = (from table in db.Guests where table.GuestType == 0 select table);
                if (!GuestID.Equals(string.Empty))
                    query = query.Where(table => table.GuestID.ToLower().Equals(GuestID.ToLower()));
                if (!Name.Equals(string.Empty))
                    query = query.Where(table => (table.FirstName.Contains(Name.ToLower()) || table.Lastname.Contains(Name.ToLower())));
                if (!Email.Equals(string.Empty))
                    query = query.Where(table => table.Email.ToLower().Equals(Email.ToLower()));
                if (!AddressLine1.Equals(string.Empty))
                    query = query.Where(table => table.AddressLine1.ToLower().Equals(AddressLine1.ToLower()));
                if (!AddressLine2.Equals(string.Empty))
                    query = query.Where(table => table.AddressLine2.ToLower().Equals(AddressLine2.ToLower()));
                if (!Tel.Equals(string.Empty))
                    query = query.Where(table => table.Tel.ToLower().Equals(Tel.ToLower()));
                if (!Fax.Equals(string.Empty))
                    query = query.Where(table => table.Fax.ToLower().Equals(Fax.ToLower()));
                for (int i = SortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(SortClause.ElementAt(i));
                Count = query.Count();

                if (Page.HasValue && Row.HasValue)
                    query = query.PageIQuery(Page.Value, Row.Value);
                
                query = query.OrderBy(table => table.GuestID);
                
                foreach (var item in query)
                {
                    item.HasBooking = db.Reservations.Count(x => x.GuestID == item.GuestID) > 0;
                    list.Add(item);
                }

            }
            return list;
        }

        /// <summary>
        /// New Function
        /// </summary>
        /// <param name="guestID"></param>
        /// <param name="boatID"></param>
        /// <returns></returns>
        public List<Guest> FetchAgentList(string guestID)
        {
            List<Guest> list = new List<Guest>();

            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                db.DeferredLoadingEnabled = false;
                // Only get agent type
                var query = (from table in db.Guests where table.GuestType == 0 && table.GuestID.ToLower().StartsWith(guestID.ToLower().Trim()) select table);
                if (query != null)
                    list = query.Take(20).ToList();

            }
            return list;
        }

        /// <summary>
        /// Get list aspnet_user
        /// </summary>
        /// <returns></returns>
        public List<aspnet_User> GetListaspnet_User()
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                var query = from u in db.aspnet_Users
                            join g in db.Guests on u.UserId equals g.UserId into g1
                            from g2 in g1.DefaultIfEmpty()
                            where g2 == null
                            select u;
                return query.ToList();
            }
        }

        #region Extensibility Method Declarations
        public void InsertCollection(List<Guest> ls)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                db.Guests.InsertAllOnSubmit(ls);
                db.SubmitChanges();
            }
        }
        public void Update(Guest item)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                db.UpdateGuest(item.GuestID, item.UserId, item.CompanyName, item.Title, item.FirstName, item.Lastname, item.CountryID, item.Email, item.AddressLine1, item.AddressLine2, item.ZipCode, item.City, item.Tel, item.Fax, item.Active, item.GuestType);
            }
        }
        public List<Guest> GetGuestAll()
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                return db.Guests.ToList();
            }
        }

        public Guest GetGuestById(string pGuestID)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                return db.Guests.SingleOrDefault(a => a.GuestID.Equals(pGuestID));
            }
        }

        public List<Guest> SelectListItem(string GuestID, System.Guid? UserId, string CompanyName, string Title, string FirstName, string Lastname, int? CountryID, string Email, string AddressLine1, string AddressLine2, string ZipCode, string City, string Tel, string Fax, bool? Active, int? GuestType, HashSet<string> SortClause)
        {
            int Count = 0;
            return SelectListItem(GuestID, UserId, CompanyName, Title, FirstName, Lastname, CountryID, Email, AddressLine1, AddressLine2, ZipCode, City, Tel, Fax, Active, GuestType, null, null, out Count, SortClause);
        }
        public List<Guest> SelectListItem(string GuestID, System.Guid? UserId, string CompanyName, string Title, string FirstName, string Lastname, int? CountryID, string Email, string AddressLine1, string AddressLine2, string ZipCode, string City, string Tel, string Fax, bool? Active, int? GuestType, int? Page, int? Row, out int Count, HashSet<string> SortClause)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                var query = (from table in db.Guests select table);
                if (!GuestID.Equals(string.Empty))
                    query = query.Where(table => table.GuestID.ToLower().Equals(GuestID.ToLower()));
                if (UserId.HasValue)
                    query = query.Where(table => table.UserId == UserId.Value);
                if (!CompanyName.Equals(string.Empty))
                    query = query.Where(table => table.CompanyName.ToLower().Equals(CompanyName.ToLower()));
                if (!Title.Equals(string.Empty))
                    query = query.Where(table => table.Title.ToLower().Equals(Title.ToLower()));
                if (!FirstName.Equals(string.Empty))
                    query = query.Where(table => table.FirstName.ToLower().Equals(FirstName.ToLower()));
                if (!Lastname.Equals(string.Empty))
                    query = query.Where(table => table.Lastname.ToLower().Equals(Lastname.ToLower()));
                if (CountryID.HasValue)
                    query = query.Where(table => table.CountryID == CountryID.Value);
                if (!Email.Equals(string.Empty))
                    query = query.Where(table => table.Email.ToLower().Equals(Email.ToLower()));
                if (!AddressLine1.Equals(string.Empty))
                    query = query.Where(table => table.AddressLine1.ToLower().Equals(AddressLine1.ToLower()));
                if (!AddressLine2.Equals(string.Empty))
                    query = query.Where(table => table.AddressLine2.ToLower().Equals(AddressLine2.ToLower()));
                if (!ZipCode.Equals(string.Empty))
                    query = query.Where(table => table.ZipCode.ToLower().Equals(ZipCode.ToLower()));
                if (!City.Equals(string.Empty))
                    query = query.Where(table => table.City.ToLower().Equals(City.ToLower()));
                if (!Tel.Equals(string.Empty))
                    query = query.Where(table => table.Tel.ToLower().Equals(Tel.ToLower()));
                if (!Fax.Equals(string.Empty))
                    query = query.Where(table => table.Fax.ToLower().Equals(Fax.ToLower()));
                if (Active.HasValue)
                    query = query.Where(table => table.Active == Active.Value);
                if (GuestType.HasValue)
                    query = query.Where(table => table.GuestType == GuestType.Value);
                for (int i = SortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(SortClause.ElementAt(i));
                Count = query.Count();
                if (Page.HasValue && Row.HasValue)
                    return query.PageIQuery(Page.Value, Row.Value).ToList();
                return query.ToList();
            }
        }
        #endregion
    }
}